SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

DECLARE @AlteredFlag BIT
SELECT @AlteredFlag	= 0;

IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'dbo'
				AND TABLE_NAME = 'StoreLedger'
				AND TABLE_TYPE = 'BASE TABLE' )
	-------------------------------------------------------------------------------
	--Table Already Exists - Execute ALTER Statements
	-------------------------------------------------------------------------------
	BEGIN
		/*
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Product' AND COLUMN_NAME = 'StartDate' )
		BEGIN
			ALTER TABLE dbo.Product ADD StartDate SMALLDATETIME NULL;
			PRINT 'dbo.Product.StartDate column added.';
			SET @AlteredFlag = 1;
		END
		*/
		IF ( @AlteredFlag = 1 )
			BEGIN
				PRINT 'Table StoreLedger altered with latest changes.';		
				PRINT 'Table StoreLedger successfully upgraded.';
			END
		ELSE
			BEGIN
				PRINT 'Table StoreLedger requires no changes.';
			END
		END
ELSE
	-------------------------------------------------------------------------------
	--Table Does Not Exist - Create the Table
	-------------------------------------------------------------------------------
	BEGIN
		CREATE TABLE [dbo].[StoreLedger](
			[ID] [int] IDENTITY(1,1) NOT NULL,
			[ProductID] [int] NOT NULL,
			[ReceiveDate] [datetime] NOT NULL,
			[ExpireDate] [datetime] NULL,
			[OBQuantity] [int] NOT NULL,
			[OBRate] [float] NOT NULL,
			[OBTotal] [float] NOT NULL,
			[ReceiveQuantity] [int] NOT NULL,
			[ReceiveRate] [float] NOT NULL,
			[ReceiveTotal] [float] NOT NULL,
			[IssueQuantity] [int] NOT NULL,
			[IssueRate] [float] NOT NULL,
			[IssueTotal] [float] NOT NULL,
			[TransactionDetailID] [int] NOT NULL,
			[TransactionDate] [datetime] NULL,
			[BaseUomNo] [int] NULL,
			[TransactionID] [int] NOT NULL,
			[CreatedOn] [datetime] NOT NULL,
			[CreatebBy] [int] NOT NULL,
			[ModifiedOn] [datetime] NOT NULL,
			[ModifiedBy] [int] NOT NULL,
		 CONSTRAINT [PK_dbo_StorLedger_1] PRIMARY KEY NONCLUSTERED 
		(
			[ID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
		) ON [PRIMARY]

		--GO

		ALTER TABLE [dbo].[StoreLedger]  WITH CHECK ADD  CONSTRAINT [FK_StoreLedger_Product] FOREIGN KEY([ProductID])
		REFERENCES [dbo].[Product] ([ID])
		--GO

		ALTER TABLE [dbo].[StoreLedger] CHECK CONSTRAINT [FK_StoreLedger_Product]
		--GO

		ALTER TABLE [dbo].[StoreLedger]  WITH CHECK ADD  CONSTRAINT [FK_StoreLedger_Transaction] FOREIGN KEY([TransactionID])
		REFERENCES [dbo].[Transaction] ([ID])
		--GO

		ALTER TABLE [dbo].[StoreLedger] CHECK CONSTRAINT [FK_StoreLedger_Transaction]
		--GO

		ALTER TABLE [dbo].[StoreLedger]  WITH CHECK ADD  CONSTRAINT [FK_StoreLedger_TransactionDetail] FOREIGN KEY([TransactionDetailID])
		REFERENCES [dbo].[TransactionDetail] ([ID])
		--GO

		ALTER TABLE [dbo].[StoreLedger] CHECK CONSTRAINT [FK_StoreLedger_TransactionDetail]
		--GO
				
	-------------------------------------------------------------------------------
	--	END OF CREATE STATEMENTS.
	--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
	-------------------------------------------------------------------------------
	-- Make sure object was created successfully.
	IF EXISTS (	SELECT *
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_SCHEMA = 'dbo'
					AND TABLE_NAME = 'StoreLedger'
					AND TABLE_TYPE = 'BASE TABLE' )


		BEGIN
			PRINT 'Table StoreLedger created successfully.';	
		END
	ELSE
		BEGIN
			PRINT 'ERROR: Failed to create Table StoreLedger.';
		END
	END
GO



